package com.ltz.service.report;

import java.io.IOException;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.stereotype.Service;

import util.Constants;
import util.DateUtil;
import util.ExcelUtil;

import com.ltz.dao.report.MonthlyProductDao;
import com.ltz.dao.well.WellDao;
import com.ltz.model.MonthlyProductData;
import com.ltz.model.Page;
import com.ltz.model.User;
import com.ltz.model.WellBasicData;
import com.ltz.model.WellGroup;

@Service("reportService")
public class MonthlyProductService {
	@Resource
	private MonthlyProductDao monPrdDao;
	@Resource
	private WellDao wellDao;

	public Page loadList(Page page,String queryStr){
		String sql="select rec.*,w.name as wellName from monthly_product_data rec left join well_basic_data w on rec.well_team_id=w.id where w.name like ? order by rec.date_input desc";
		return monPrdDao.loadList(page,sql,new Object[]{"%"+queryStr+"%"});
	}
	private List loadList(String queryStr){
		String sql="select rec.*,w.name as wellName from monthly_product_data rec left join well_basic_data w on rec.well_team_id=w.id where w.name like ? order by rec.date_input desc";
		return monPrdDao.nativeQueryForList(sql,new Object[]{"%"+queryStr+"%"},MonthlyProductData.class);
	}
	public boolean doAddMonPrd(User user,MonthlyProductData w){
		w.setId(null);
		w.setDateInput(new Date());
		w.setOwner(user.getUsername());
		monPrdDao.add(w);
		return true;
	}
	public boolean doUpdateMonPrd(User user,MonthlyProductData w){
		MonthlyProductData rec=(MonthlyProductData) monPrdDao.get(MonthlyProductData.class, w.getId());
		w.setDateInput(rec.getDateInput());
		w.setOwner(rec.getOwner());
		monPrdDao.update(w);
		return true;
	}
	public boolean doDeleteMonPrd(int recordId){
		monPrdDao.delete(MonthlyProductData.class, recordId);
		return true;
	}
	public List getWellList(){
		return wellDao.getWellList();
	}
	public void exportExcel(HttpServletRequest request, HttpServletResponse response){
		//根据条件查询出要导出的结果集
		String qs=request.getParameter("queryStr");
		List<MonthlyProductData> list=this.loadList(qs);
		// 生成提示信息，  
		response.setContentType("application/vnd.ms-excel");  
		String codedFileName = null;  
		OutputStream fOut = null;  
		try  
		{  
			// 进行转码，使其支持中文文件名  
			codedFileName = java.net.URLEncoder.encode("月度汇总报表_"+DateUtil.formatDate(new Date(), "yyyyMMdd"), "UTF-8");  
			response.setHeader("content-disposition", "attachment;filename=" + codedFileName + ".xls");  
			// response.addHeader("Content-Disposition", "attachment;   filename=" + codedFileName + ".xls");  
			// 产生工作簿对象  
			Map<String,String> titleMap=new LinkedHashMap<String,String>();
			titleMap.put("date", "日期");
			titleMap.put("wellName", "井名");
			titleMap.put("waterIn", "注水量（方）");
			titleMap.put("preduction", "生产量（方）");
			titleMap.put("oilIn", "注油量（方）");
			titleMap.put("na2so4PerL", "硫酸钠含量（克/升）");
			titleMap.put("naclPerL", "氯化钠含量（克/升）");
			titleMap.put("naclWaterPerL", "来水氯化钠含量（克/升）");
			titleMap.put("na2so4Total", "硫酸钠固体含量(千克)");
			titleMap.put("naclTotal", "氯化钠固体含量（千克）");
			titleMap.put("naclTotalNoWater", "去掉来水氯化钠固体含量（千克）");
			titleMap.put("pipePressureIn", "进口管压（Mpa）");
			titleMap.put("pressureBack", "回压（Mpa）");
			titleMap.put("waterTemperature", "注水温度（°C）");
			titleMap.put("na2so4Temperature", "出硝温度（°C)");
			titleMap.put("operator", "统计人");
			titleMap.put("auditer", "审核人");
			titleMap.put("remark", "备注");
			titleMap.put("produceMethod", "注采方式");
			titleMap.put("dateInput", "数据录入时间");
			titleMap.put("owner", "录入人");
			HSSFWorkbook workbook=ExcelUtil.getBook(titleMap, list);
			fOut = response.getOutputStream();  
			workbook.write(fOut);  
		}  
		catch (UnsupportedEncodingException e1)  
		{
			e1.printStackTrace();
		}  
		catch (Exception e)  
		{
			e.printStackTrace();
		}  
		finally  
		{  
			try  
			{  
				fOut.flush();  
				fOut.close();  
			}  
			catch (IOException e)  
			{
				e.printStackTrace();
			}  
		}  
		System.out.println("文件生成...");
	}
	//flex接口
	public List getWellGroupList(){
		List list=wellDao.getAll(WellGroup.class);
		return list;
	}
	/**
	 * 单个井组各个物质之间
	 * @param wellGroupId
	 * @param year
	 * @param timeType
	 * @return
	 */
	public List getSingleWellGroupReport(int wellGroupId,int year,int timeType){
		String sql="";
		if(timeType==Constants.TIME_TYPE_MONTH){
			sql="select MONTH(prd.date) reportDate,"+ 
			"g.well_group_name wellTeamName,"+
			"sum(ifnull(water_in,0)) waterIn,"+
			"sum(ifnull(oil_in,0)) oilIn,"+
			"sum(ifnull(preduction,0)) preduction,"+
			"sum(ifnull(na2so4_per_l,0)) na2so4PerL,"+
			"sum(ifnull(nacl_per_l,0)) naclPerL,"+
			"sum(ifnull(nacl_water_per_l,0)) naclWaterPerL,"+
			"sum(ifnull(na2so4_total,0)) na2so4Total,"+
			"sum(ifnull(nacl_total,0)) naclTotal,"+
			"sum(ifnull(nacl_total_no_water,0)) naclTotalNoWater"+
			" from monthly_product_data as prd inner join mn_well_group as g on prd.well_team_id=g.well_group_id and g.well_group_id=? where YEAR(prd.date)=? group by reportDate order by reportDate asc;";
		}else if(timeType==Constants.TIME_TYPE_QUARTER){
			sql="select QUARTER(prd.date) reportDate,"+ 
			"g.well_group_name wellTeamName,"+
			"sum(ifnull(water_in,0)) waterIn,"+
			"sum(ifnull(oil_in,0)) oilIn,"+
			"sum(ifnull(preduction,0)) preduction,"+
			"sum(ifnull(na2so4_per_l,0)) na2so4PerL,"+
			"sum(ifnull(nacl_per_l,0)) naclPerL,"+
			"sum(ifnull(nacl_water_per_l,0)) naclWaterPerL,"+
			"sum(ifnull(na2so4_total,0)) na2so4Total,"+
			"sum(ifnull(nacl_total,0)) naclTotal,"+
			"sum(ifnull(nacl_total_no_water,0)) naclTotalNoWater"+
			" from monthly_product_data as prd inner join mn_well_group as g on prd.well_team_id=g.well_group_id and g.well_group_id=? where YEAR(prd.date)=? group by reportDate order by reportDate asc;";
		}
		List list=wellDao.nativeQueryForList(sql, new Object[]{wellGroupId,year}, MonthlyProductData.class);
		return list;
	}
	/**
	 * 单个物质个井组之间比较
	 * @return
	 */
	public List getMoreWellGroupReport(int productType,int year,int timeType){
		//初始化动态数组
		List<WellGroup> list=this.getWellGroupList();
		
		String sql="select well_team_id wellTeamId,";
		//产物
		switch(productType){
		case Constants.PRODUCT_TYPE_WATERIN:
			sql+="sum(ifnull(water_in,0)) value";
			break;
		case Constants.PRODUCT_TYPE_OILIN:
			sql+="sum(ifnull(oil_in,0)) value";
			break;
		case Constants.PRODUCT_TYPE_PREDUCTION:
			sql+="sum(ifnull(preduction,0)) value";
			break;
		case Constants.PRODUCT_TYPE_NA2SO4PERL:
			sql+="sum(ifnull(na2so4_per_l,0)) value";
			break;
		case Constants.PRODUCT_TYPE_NACLPERL:
			sql+="sum(ifnull(nacl_per_l,0)) value";
			break;
		case Constants.PRODUCT_TYPE_NACLWATERPERL:
			sql+="sum(ifnull(nacl_water_per_l,0)) value";
			break;
		case Constants.PRODUCT_TYPE_NA2SO4TOTAL:
			sql+="sum(ifnull(na2so4_total,0)) value";
			break;
		case Constants.PRODUCT_TYPE_NACLTOTAL:
			sql+="sum(ifnull(nacl_total,0)) value";
			break;
		case Constants.PRODUCT_TYPE_NACLTOTALNOWATER:
			sql+="sum(ifnull(nacl_total_no_water,0)) value";
			break;
		}
		//周期
		switch(timeType){
		case Constants.TIME_TYPE_MONTH:
			sql+=",MONTH(date) reportDate from monthly_product_data where YEAR(date)=? and MONTH(date)=? group by well_team_id order by reportDate asc";
			break;
		case Constants.TIME_TYPE_QUARTER:
			sql+=",QUARTER(date) reportDate from monthly_product_data where YEAR(date)=? and QUARTER(date)=? group by well_team_id order by reportDate asc";
			break;
		}
		List<Map<Object,Object>> result=new ArrayList<Map<Object,Object>>();
		//暂时只是循环次数有区别而已，如果以后优化没可以考虑去掉
		if(timeType==Constants.TIME_TYPE_MONTH){
			List<Map<String,Object>> tmp;
			Object[] tmpM;
			Map<Object,Object> resultMap;
			for(int i=1; i<=12; i++){
				resultMap=new HashMap<Object,Object>();
				resultMap.put("date", i);
				Map<Object,Object[]> map=new HashMap<Object,Object[]>();
				for(WellGroup g:list){
					map.put("group"+g.getWellGroupId(), new Object[]{g.getWellGroupName(),0.0});
				}
				tmp=wellDao.queryForList(sql, year,i);
				if(null!=tmp){
					for(int j=0; j<tmp.size(); j++){
						//给对应井组赋值
						tmpM=map.get("group"+tmp.get(j).get("wellTeamId").toString());
						if(null!=tmpM){
							tmpM[1]=Double.parseDouble(tmp.get(j).get("value").toString());
						}
					}
				}
				resultMap.putAll(map);
				result.add(resultMap);
			}
		}else if(timeType==Constants.TIME_TYPE_QUARTER){
			List<Map<String,Object>> tmp;
			Object[] tmpM;
			Map<Object,Object> resultMap;
			for(int i=1; i<=4; i++){
				resultMap=new HashMap<Object,Object>();
				resultMap.put("date", i);
				Map<Object,Object[]> map=new HashMap<Object,Object[]>();
				for(WellGroup g:list){
					map.put("group"+g.getWellGroupId(), new Object[]{g.getWellGroupName(),0.0});
				}
				tmp=wellDao.queryForList(sql, year,i);
				if(null!=tmp){
					for(int j=0; j<tmp.size(); j++){
						//给对应井组赋值
						tmpM=map.get("group"+tmp.get(j).get("wellTeamId").toString());
						if(null!=tmpM){
							tmpM[1]=Double.parseDouble(tmp.get(j).get("value").toString());
						}
					}
				}
				resultMap.putAll(map);
				result.add(resultMap);
			}
		}
		return result;
	}
}
